package ua.edu.test.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


/**
 * Created by Михаил on 20.06.2014.
 */
public class BankDB extends SQLiteOpenHelper {

    public static final String MatrixX = "MatrixX";
    public static final String BLOCK = "BLOCK";
    public static final String UNBLOCK = "UNBLOCK";
    public static final String MOBI = "MOBI";
    public static final String SEND = "SEND";
    public static final String TABLE_CARDS = "CARDS";
    public static final String ID = "ID";
    public static final String BANK = "BANK";
    public static final String NUMBER = "NUMBER";
    public static final String NAME = "NAME";
    public static final String BALANCE = "BALANCE";
    private static final String SQL_CREATE_MatrixX =
            "CREATE TABLE " + MatrixX +
                    " (" + BANK + " VARCHAR(30) PRIMARY KEY, " +
                    BALANCE + " INTEGER NOT NULL, " +
                    BLOCK + " INTEGER NOT NULL, " +
                    UNBLOCK + " INTEGER NOT NULL, " +
                    MOBI + " INTEGER NOT NULL, " +
                    SEND + " INTEGER NOT NULL); ";
    private static String SQL_INSERT_INTO_MatrixX;
    public static final String LAST_DATE = "LAST_DATE";
    private static final String SQL_CREATE_CARDS =
            "CREATE TABLE " + TABLE_CARDS + " (" +
                    ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    BANK + " VARCHAR(30) NOT NULL, " +
                    NUMBER + " VARCHAR(6) NOT NULL, " +
                    NAME + " VARCHAR(50) NOT NULL," +
                    BALANCE + " REAL, " +
                    LAST_DATE + " VARCHAR(10)); ";
    public static final String TABLE_HISTORY = "HISTORY";
    public static final String ID_CARD = "ID_CARD";
    public static final String ACTION = "ACTION";
    private static final String SQL_CREATE_HISTORY =
            "CREATE TABLE " + TABLE_HISTORY + " (" +
                    ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    ID_CARD + " INTEGER NOT NULL, " +
                    ACTION + " VARCHAR(50) NOT NULL); ";
    public static final String TABLE_BANKS = "BANKS";
    public static final String INFO = "INFO";
    private static final String SQL_CREATE_BANKS =
            "CREATE TABLE " + TABLE_BANKS +
                    " (" + ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    NAME + " VARCHAR(30) NOT NULL," +
                    INFO + " VARCHAR(500) NOT NULL); ";
    private static String SQL_INSERT_INTO_BANKS;
    private static final String DATABASE_NAME = "SMS_Banking.db";
    private static final int DATABASE_VERSION = 1;
    private static final String SQL_DELETE_ENTRIES = "DROP TABLE IF EXISTS ";
    private static BankDB instance = null;

    private static String creatSupportSQLINSERTBanks() {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (1, 'ПриватБанк', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (2, 'Укрсоцбанк(UniCredit)', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (3, 'УкрСиббанк', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (4, 'Укрэксимбанк', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (5, 'Аваль', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (6, 'Правэкс-Банк', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (7, 'ПУМБ', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (8, 'Кредобанк', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (9, 'Марфин Банк', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (10, 'Профин банк', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (11, 'Укринбанк', '');").
                append("INSERT INTO " + TABLE_BANKS + " " +
                        "(" + ID + ", " + NAME + ", " + INFO + ")" +
                        "VALUES (12, 'Укргазбанк', '');");
        return stringBuilder.toString();
    }

    private static String creatSupportSQLINSERTMatrix() {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder
                .append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('ПриватБанк', 1,  1,  1,   1,   1) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('Укрсоцбанк(UniCredit)', 1,   1,  0,  0,  1) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('УкрСиббанк', 1,   1,  1,   0,  0) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('Укрэксимбанк', 1,   1,  0,  0,  0) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('Аваль', 1,   1,  0,  0,  0) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('Правэкс-Банк', 1,   1,  0,  0,  0) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('ПУМБ', 1,  1,  0,  0,  0) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('Кредобанк', 1,  0, 0,  0,  0) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('Марфин Банк', 1,  0, 0,  0,  0) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('Профин банк', 1,  1,  0,  0,  0) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('Укринбанк', 0,  0, 0,  1,   0) ;").
                append("INSERT INTO " + MatrixX + " " +
                        "(" + BANK + ", " + BALANCE + ", " + BLOCK + ", " + UNBLOCK + ", " + MOBI + ", " + SEND + ")" +
                        "VALUES ('Укргазбанк', 1,   1,  1,   0,  0) ;");
        return stringBuilder.toString();
    }

    private BankDB(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public static synchronized BankDB getInstance(Context context) {
        if (instance == null) {
            instance = new BankDB(context);
        }
        return instance;
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(SQL_CREATE_CARDS);
        sqLiteDatabase.execSQL(SQL_CREATE_BANKS);
        sqLiteDatabase.execSQL(SQL_CREATE_HISTORY);
        sqLiteDatabase.execSQL(SQL_CREATE_MatrixX);




        sqLiteDatabase.execSQL(creatSupportSQLINSERTBanks());
        sqLiteDatabase.execSQL(creatSupportSQLINSERTMatrix());
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i2) {
        // Удаляем предыдущую таблицу при апгрейде
        sqLiteDatabase.execSQL(SQL_DELETE_ENTRIES + TABLE_CARDS);
        sqLiteDatabase.execSQL(SQL_DELETE_ENTRIES + TABLE_BANKS);
        sqLiteDatabase.execSQL(SQL_DELETE_ENTRIES + MatrixX);
        // Создаём новый экземпляр таблицы
        onCreate(sqLiteDatabase);
    }

    @Override
    public void onDowngrade(SQLiteDatabase sqLiteDatabase, int i, int i2) {
        // Удаляем предыдущую таблицу
        sqLiteDatabase.execSQL(SQL_DELETE_ENTRIES + TABLE_CARDS);
        sqLiteDatabase.execSQL(SQL_DELETE_ENTRIES + TABLE_BANKS);
        sqLiteDatabase.execSQL(SQL_DELETE_ENTRIES + MatrixX);
        // Создаём новый экземпляр таблицы
        onCreate(sqLiteDatabase);
    }
}